package com.mybooksystem.springboot.Dao.visualize;

import com.mybooksystem.springboot.pojo.visualize.classForAll.CoursesAndCredits;
import com.mybooksystem.springboot.pojo.visualize.classForAll.StudentDataFormClassForAll;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 单个专业的可视化表格数据 [mapper 层]
 */
@Repository
@Mapper
public interface ClassForAllMapper {


    /**
     *根据 专业编号 获取 【学号、姓名、性别、班级、科目名、分数】列表。
     * @param proId：要获取的 专业编号。
     * @return
     *      返回结果集。
     */
    @Select("SELECT DISTINCT stu.studentId, stu.studentName,stu.gender,class_pro.className, pro.courseName, stu_sub.subjectsScore " +
                "FROM " +
            "students_and_subjectscore_relation stu_sub " +

                "LEFT JOIN " +
                "students stu " +
                "ON " +
                "stu.studentId = stu_sub.studentId " +

                    "LEFT JOIN " +
                    "pro_and_course_relation pro " +
                    "ON " +
                    "pro.courseID = stu_sub.courseID " +

                        "LEFT JOIN " +
                        "class_and_pro_relation class_pro " +
                        "ON " +
                        "stu_sub.proId = class_pro.proId AND stu_sub.classId = class_pro.classId " +
            "WHERE " +
                "stu_sub.proId = #{proId} ")
    List<StudentDataFormClassForAll> getData(String proId);

    /**
     * 根据 专业编号 获取 【科目名】列表。
     * @param proId：专业编号。
     * @return
     *      返回结果集。
     */
    @Select("SELECT courseName, credits " +
                "FROM " +
            "pro_and_course_relation " +
            "WHERE " +
                "proId = #{proId}")
    List<CoursesAndCredits> getCourseListAndCredits(String proId);
}
